﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace SqliteLoader
{
	public class ColumnInfo
	{
		public int Cid { get; set; }
		public string Name { get; set; }
		public string DataType { get; set; }
		public string DefaultValue { get; set; }
		public bool IsPrimaryKey { get; set; }
		public bool NotNull { get; set; }
	}

	public static class SqliteUtil
	{
		public static IEnumerable<string> GetTableNames(SQLiteConnection connection)
		{
			using (var cmd = connection.CreateCommand())
			{
				cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
				using (var reader = cmd.ExecuteReader())
					while (reader.Read())
						yield return reader.GetString(0);
			}
		}

		public static IEnumerable<ColumnInfo> GetColumnInfos(SQLiteConnection connection, string table)
		{
			using (var cmd = connection.CreateCommand())
			{
				cmd.CommandText = string.Format("PRAGMA table_info({0})", table);
				using (var reader = cmd.ExecuteReader())
					while (reader.Read())
						yield return new ColumnInfo()
						{
							Cid = Convert.ToInt32(reader["cid"]),
							Name = Convert.ToString(reader["name"]),
							DataType = Convert.ToString(reader["type"]),
							NotNull = Convert.ToBoolean(reader["notnull"]),
							DefaultValue = Convert.ToString(reader["dflt_value"]),
							IsPrimaryKey = Convert.ToBoolean(reader["pk"])
						};
			}
		}

		public static IEnumerable<IndexInfo> GetIndexInfos(SQLiteConnection connection, string table)
		{
			using (var cmd = connection.CreateCommand())
			{
				cmd.CommandText = string.Format("PRAGMA index_list({0})", table);
				using (var reader = cmd.ExecuteReader())
					while (reader.Read())
						yield return new IndexInfo()
						{
							Seq = Convert.ToInt32(reader["seq"]),
							Name = Convert.ToString(reader["name"]),
							Unique = Convert.ToBoolean(reader["unique"])
						};
			}
		}

		public static string GetValidTableName(string name)
		{
			if(string.IsNullOrEmpty(name)) return null;

			var regex = new Regex(@"(^[0-9]+|\W+|^sqlite_)");
			name = regex.Replace(name, "_");
			name = name.Trim('_');
			if (string.IsNullOrEmpty(name)) return null;
			return name;
		}

		public static string GetValidIdentifier(string name)
		{
			if (string.IsNullOrEmpty(name)) return null;

			var regex = new Regex(@"(^[0-9]+|\W+)");
			name = regex.Replace(name, "_");
			name = name.Trim('_');
			if (string.IsNullOrEmpty(name)) return null;
			return name;
		}
	}
}
